package Classes;

import Forms.ServerFrame;
import java.sql.*;
import javax.swing.JOptionPane;
import java.util.*;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author mau
 */
public class Database {
    static ServerFrame sf = null;
    String url = "jdbc:mysql://localhost:3306/meab";
    String driver = "com.mysql.jdbc.Driver";
    String un = "root";
    String pw = "";
    ArrayList<String> usernames = null;
    int rowCount = 0;
    static ArrayList<Event> event = null;
    static ArrayList<SumEvent> sEvent = null;
    
    static Connection c = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;
    
    static ArrayList<User> users = null;
    static ArrayList<User> usersList = null;
    static ArrayList<User> userEdit = null;
    
    static ArrayList<Curriculum> curr = null;
    static ArrayList<Curriculum> currS = null;

    static ArrayList<ClassLevel> classLevel = null;
    static ArrayList<Subject> subject = null;
    
   static ArrayList<Grades1> gradesS = null;
    static ArrayList<Grades1> gradesSS = null;
    static ArrayList<Grades1> grades = null;
    
    static ArrayList<Sections> cbs = null;
    
    static ArrayList<SchoolYear> schoolyear = null;
    static ArrayList<SummerSy> summerSy = null;
    
    public Database(ServerFrame a) {
        try {
            Class.forName(driver).newInstance();
            c = (Connection) DriverManager.getConnection(url, un, pw);
            sf = a;
            sf.appendTextArea("Database Connection Successful. Server is ready.");
        } catch (Exception e) {
//            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Please start wamp server", "Error", JOptionPane.ERROR_MESSAGE);
            System.exit(0);
        }
    }
     
    public User getUser(String username) {
        User user = null;
        try{
            ps = c.prepareStatement("Select userID, userLName, userGName, userMName, userName, password, userStatus, userType From user LEFT JOIN usert ON user.usertID = usert.usertID where userName = '" + username + "'");
            rs = ps.executeQuery();

            if (rs != null) {
                while(rs.next()) {
                    user = new User(rs.getString(1), rs.getString(2) , rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                    user.toString();
                }
            }
        } catch(Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }         
        return user;
    } 
    
    public void disconnect() {
        try {
            c.close();
            ServerFrame.serverArea.append("\nDatabase server has stopped running.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void changeUserStatus(String username, String status) throws Exception {
        ps = c.prepareStatement("Update User Set userStatus = '" + status + "' where username = '" + username + "'");
        int r = ps.executeUpdate();
        
    }
    
    public boolean checkStatus(String username) throws Exception {
        ps = c.prepareStatement("select userStatus from user where username = '" + username + "'");
        rs = ps.executeQuery();
        rs.next();
        boolean in = false;
        if(rs.getString(1).equals("In")) {
            in = true;
        }
        return in;
    }    
    
    
    public String userType(String username) throws Exception {
        ps = c.prepareStatement("select userType from user where username = '" + username + "'");
        rs = ps.executeQuery();
        String ut = null;
            while(rs.next()) {
                ut = rs.getString(1);
            }
        return ut;
    }
    
    public ArrayList<Object> getList(String table) {
        ArrayList<Object> result = null;
        if(table.equals("getlist-users")) {
            try {
                ps = c.prepareStatement("Select userID, userLName, userGName, userMName, userName, password, userStatus, userType From user LEFT JOIN usert ON user.usertID = usert.usertID");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        User u = new User(rs.getString(1), rs.getString(2) , rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        result.add(u);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist-curriculums")) {
            try {
                ps = c.prepareStatement("Select * from curriculum");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Curriculum cu = new Curriculum(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                        result.add(cu);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist-curriculumsy")) {
            try {
                ps = c.prepareStatement("Select schoolyear.name, curriculum.currName, curriculum.dateEncoded, curriculum.desc, curriculum.dateEffective, currsy.currsyID, currsy.currID, currsy.syID from curriculum left join currsy on curriculum.currID = currsy.currID left join schoolyear on schoolyear.syID = currsy.syID order by 1 desc");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Curriculum cu = new Curriculum(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getInt(8));
                        result.add(cu);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist-sylist")) {
            try {
                ps = c.prepareStatement("Select syID, name from schoolyear");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        SchoolYear sy = new SchoolYear(rs.getInt(1), rs.getString(2));
                        result.add(sy);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist-classlevels")) {
            try {
                ps = c.prepareStatement("Select classID, className, clevelcat.categoryName from classlevel join clevelcat on classlevel.clcatID = clevelcat.clcatID");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel c = new ClassLevel(rs.getInt(1), rs.getString(2), rs.getString(3));
                        result.add(c);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        // APRIL 23
        if (table.equals("getlist-event")) {
            try {
                ps = c.prepareStatement("SELECT schoolyear.name, event.eveName FROM schoolyear JOIN event ON schoolyear.syID = event.syID");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Event ev = new Event(rs.getString(1), rs.getString(2));
                        result.add(ev);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // end
        
                
        // APRIL 25
        if (table.equals("getlist-currCb")) {
            try {
                ps = c.prepareStatement("SELECT currName FROM curriculum");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                while (rs.next()) {
                    String cu = rs.getString(1);
                    result.add(cu);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("get-classlevel")) {
            try {
                ps = c.prepareStatement("SELECT className FROM classlevel");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String cl = rs.getString(1);
                        result.add(cl);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist-accounts")) {
            try {
                ps = c.prepareStatement("SELECT userName, userType FROM user JOIN usert ON user.usertID = usert.usertID;");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        User us = new User(rs.getString(1), rs.getString(2));
                        result.add(us);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist-students")) {
            try {
                ps = c.prepareStatement("SELECT IDNo, stuLName, stuGName, stuMName, schoolyear.name, curriculum.currName, className, section.secName, oldGenAverage, status, stuID FROM student LEFT JOIN section ON student.secID = section.secID LEFT JOIN classlevel ON student.classID = classlevel.classID LEFT JOIN schoolyear ON student.syID = schoolyear.syID left join curriculum on student.currID = curriculum.currID");
                //ps = c.prepareStatement("SELECT IDNo, stuLName, stuGName, stuMName, schoolyear.name, curriculum.currName, className, section.secName, oldGenAverage, status FROM student LEFT JOIN section ON student.secID = section.secID LEFT JOIN classlevel ON student.classID = classlevel.classID LEFT JOIN schoolyear ON student.syID = schoolyear.syID JOIN curriculum ON student.currID = curriculum.currID");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {                        
                        Student s = new Student(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist-acctType")) {
            try {
                ps = c.prepareStatement("SELECT userType FROM usert");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String ut = rs.getString(1);
                        result.add(ut);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist-section")) {
            try {
                ps = c.prepareStatement("SELECT secName FROM section");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String sec = rs.getString(1);
                        result.add(sec);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // end
        
        if (table.equals("getlist-sections")) {
            try {
                ps = c.prepareStatement("Select secName from section");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {                        
                        Sections s = new Sections(rs.getString(1));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        
        if(table.equals("quarter")) {
            try {
                ps = c.prepareStatement("Select * from quarter");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Quarter q = new Quarter(rs.getString(1), rs.getString(2) , rs.getString(3));
                        result.add(q);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("promoteStudent-elem")) {
            try {
                //ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, className, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID where oldGenAverage > 74 ORDER BY schoolyear.name desc");
                ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, className, clevelcat.categoryName, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID join clevelcat on classlevel.clcatID = clevelcat.clcatID where oldGenAverage > 74 and categoryName = 'Elementary' OR categoryName = 'Pre-elementary' ORDER BY className desc;");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                HashMap<String, Student> map = new HashMap<String, Student>();
                if (rs != null) {
                    while (rs.next()) {
                        Student st = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
                        if(map.isEmpty()) {
                            map.put(st.getSID(), st);
                        } else {
                            if(!map.containsKey(st.getSID())) map.put(st.getSID(), st);
                        }             
                    }
                }
                Iterator iter = map.entrySet().iterator();
 
                while (iter.hasNext()) {
                        Map.Entry x = (Map.Entry) iter.next();
                        result.add((Student) x.getValue());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        //Summer
        if(table.equals("getlist-summer")) {
            try {
            ps = c.prepareStatement("SELECT sSumID, IDNo, stuLName, stuGName, stuMName, gender, parent, parentcNo, sprogsy.syName FROM studsummer LEFT JOIN sprogsy ON studsummer.spSYID = sprogsy.spSYID");
            rs = ps.executeQuery();
            result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Summer summ = new Summer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9));
                        result.add(summ);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        
        if(table.equals("gradeManage")) {
            try {
               //ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.genAverage FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID JOIN grade where s.stuID = grade.stuID");
              ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.oldGenAverage, cur.currName,s.stuID FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON s.classID = cl.classID JOIN curriculum cur ON s.currID = cur.currID ORDER BY 1");
               
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Grades1 grade2 = new Grades1(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getString(7),rs.getString(8));
                        result.add(grade2);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
          if(table.equals("attendanceManage")) {
            try {
                ps = c.prepareStatement("SELECT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Attendance attendance2 = new Attendance(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                        result.add(attendance2);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        return result;
    }
    
    public ArrayList<Object> getList2(String table, String a, String b) {
        ArrayList<Object> result = null;
        
        if (table.equals("getlist2-sec")) {
            try {
                ps = c.prepareStatement("SELECT section.secID, secName FROM section join currclsec on section.secID = currclsec.secID where currclsec.currclID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Sections s = new Sections(rs.getInt(1), rs.getString(2));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (table.equals("getlist2-subj")) {
            try {
                ps = c.prepareStatement("SELECT subject.subID, subName FROM subject join currcls on subject.subID = currcls.subID where currcls.currclID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Subject s = new Subject(rs.getString(2), rs.getString(1));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-advisers")) {
            try {
                ps = c.prepareStatement("Select user.userID, userLName, userGName, userMName, userName, password, userStatus, userType From user JOIN usert ON user.usertID = usert.usertID JOIN advisercat ON advisercat.userID = user.userID JOIN classadviser ON advisercat.caddID = classadviser.caddID where usert.userType = 'Adviser' and classadviser.caddname = '" + a + "' order by userLName");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        User u = new User(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        result.add(u);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-curriculumopensy")) {
            try {
                ps = c.prepareStatement("Select * from curriculum where dateEffective <= '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Curriculum cu = new Curriculum(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                        result.add(cu);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-currdetailscurriculum")) {
            try {
                ps = c.prepareStatement("Select currsy.currsyID, curriculum.currName, curriculum.currID, currsy.syID from curriculum join currsy on curriculum.currID = currsy.currID where currsy.syID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Curriculum c = new Curriculum(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4));
                        System.out.println("curr " + c.getCurrName());
                        result.add(c);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-clevelsy")) {
            try {
                ps = c.prepareStatement("select currcl.currclID, classlevel.classID, classlevel.className, clevelcat.categoryName from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join clevelcat on classlevel.clcatID = clevelcat.clcatID where currsy.syID = '" + a + "' order by 1");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel c = new ClassLevel(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4));
                        result.add(c);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-clsubjList")) {
            try {
                ps = c.prepareStatement("select subID, subName, subject.desc, unit, subject.classID, className from subject join classlevel on classlevel.classID = subject.classID where subject.classID = '"  + a + "' and subID not in (select subID from currcls where currclID = '" + b + "')");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Subject s = new Subject(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                        s.setClassID(rs.getString(5));
                        s.setClassName(rs.getString(6));
                        System.out.println("subj " + s.getSubjName());
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-subjforclevel")) {
            try {
                ps = c.prepareStatement("select subject.subID, subName, subject.desc, unit from subject join currcls on subject.subID = currcls.subID where currcls.currclID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Subject s = new Subject(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                        System.out.println("subj22 " + s.getSubjName());
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-clevelforcurrsy")) {
            try {
                ps = c.prepareStatement("select classlevel.classID, className from classlevel join currcl on classlevel.classID = currcl.classID where currcl.currsyID= '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel s = new ClassLevel(rs.getInt(1), rs.getString(2));
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-clsecList")) {
            try {
                ps = c.prepareStatement("select secID, secName, section.classID, className from section join classlevel on section.classID = classlevel.classID where section.classID = '"  + a + "' and secID not in (select secID from currclsec where currclID = '" + b + "')");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Sections s = new Sections(rs.getInt(1), rs.getString(2));
                        s.setClassID(rs.getString(3));
                        s.setClassLevel(rs.getString(4));
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-secforclevel")) {
            try {
                ps = c.prepareStatement("select section.secID, secName, user.userID, userLName, userGName, userMName from section join currclsec on section.secID = currclsec.secID join user on currclsec.userID = user.userID where currclsec.currclID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Sections s = new Sections(rs.getInt(1), rs.getString(2));
                        s.setAdv(new User(rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist2-clevel-subj")) {
            try {
                ps = c.prepareStatement("select subID, subName, subject.desc, unit, classID from subject where classID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Subject s = new Subject(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                        s.setClassID(rs.getString(5));
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }      
        
        if(table.equals("getlist2-clevelcurrView")) {
            try {
                ps = c.prepareStatement("select currsy.currsyID, currcl.currclID, classlevel.classID, classlevel.className, clevelcat.clcatID, clevelcat.categoryName, curriculum.currID, curriculum.currName from currsy join currcl on currsy.currsyID = currcl.currsyID join curriculum on curriculum.currID = currsy.currID join schoolyear on schoolyear.syID = currsy.syID join classlevel on classlevel.classID = currcl.classID join clevelcat on clevelcat.clcatID = classlevel.clcatID where name = '" + a + "' order by 2");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel s = new ClassLevel(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        System.out.println("cl " + s.getClassName());
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }    
        
        if(table.equals("getlist2-clevelcurrView2")) {
            try {
                ps = c.prepareStatement("select currsy.currsyID, currcl.currclID, classlevel.classID, classlevel.className, clevelcat.clcatID, clevelcat.categoryName, curriculum.currID, curriculum.currName from currsy join currcl on currsy.currsyID = currcl.currsyID join curriculum on curriculum.currID = currsy.currID join schoolyear on schoolyear.syID = currsy.syID join classlevel on classlevel.classID = currcl.classID join clevelcat on clevelcat.clcatID = classlevel.clcatID where currsy.currsyID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel s = new ClassLevel(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        System.out.println("cl2 " + s.getClassName());
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }   
        if(table.equals("getlist2-studentofsec")) {
            try {
                ps = c.prepareStatement("select stuID, IDNo, stuLName, stuMName, stuGName, oldGenAverage from student where syID = '" + a + "' and secID = '" + b + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Student s = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                        result.add(s);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }   
        return result;
    }
    
    
     public ArrayList<Object> getList3(String table, String a, String b) {
        ArrayList<Object> result = null;
        if(table.equals("subjectManage")) {
            try {
                
                //ps = c.prepareStatement("SELECT DISTINCT s.subname,c.subID FROM subject s JOIN ccs c ON s.subID = c.subID JOIN classlevel cl ON c.classID = cl.classID JOIN curriculum cc ON cc.currID = s.currID WHERE cl.className = '" + gl2 + "' AND cc.currName = '" + gl12 + "'");
                ps = c.prepareStatement("SELECT DISTINCT subject.subName, subject.subID FROM subject JOIN currcls ON subject.subID = currcls.subID JOIN currcl ON currcl.currclID = currcls.currclID JOIN classlevel ON classlevel.classID = currcl.classID JOIN currsy ON currsy.currsyID = currcl.currsyID JOIN schoolyear ON schoolyear.syID = currsy.syID JOIN curriculum ON curriculum.currID = currsy.currID WHERE classlevel.className = '" + a + "' AND curriculum.currName = '" + b + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Subject subj = new Subject(rs.getString(1),rs.getString(2));
                        result.add(subj);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (table.equals("getlist3-newstudent-secAssign")) {
            try {                
                ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, oldGenAverage, classlevel.className, schoolyear.name from student join classlevel on student.classID = classlevel.classID join schoolyear on student.syID = schoolyear.syID where className = '"+a+"' and schoolyear.name = '"+b+"'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                          Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                          result.add(s);
                      }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }        
        
        if(table.equals("getlist3-studsylevel")) {
            try {
                ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID where schoolyear.name = '"+a+"' and classlevel.className = '"+b+"' and oldGenAverage > 74");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("getlist3-studforsecassgn")) {
            try {
                String[] aa = a.split(",");
                ps = c.prepareStatement("select stuID, IDNo, stuLName, stuMName, stuGName, oldGenAverage from student where secID is null and syID = '" + aa[0] + "' and currID = '" + aa[1] + "' and classID = '" + aa[2] + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        Student s = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
                }
    
     
      public ArrayList<Object> getList4(String table, String a) {
        ArrayList<Object> result = null;
         if(table.equals("gradeManage")) {
            try {
               //ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.genAverage FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID JOIN grade where s.stuID = grade.stuID");
                //ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.oldGenAverage, cur.currName, s.stuID FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON s.classID = cl.classID JOIN curriculum cur ON cur.currID = s.currID JOIN currclsec sa ON sa.secID = sec.secID JOIN sectionadviser saa ON saa.currclsecID = sa.currclsecID JOIN user user ON user.userID = saa.userID WHERE user.userID = '" + a + "'");               
                ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.oldGenAverage, cur.currName, s.stuID FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON s.classID = cl.classID JOIN curriculum cur ON cur.currID = s.currID JOIN currclsec sa ON sa.secID = sec.secID JOIN user ON user.userID = sa.userID WHERE user.userID = '" + a + "'");               
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Grades1 grade = new Grades1(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getString(7),rs.getString(8));
                        result.add(grade);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("gradeManage1")) {
            try {
               //ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.genAverage FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID JOIN grade where s.stuID = grade.stuID");
              ps = c.prepareStatement("SELECT grade,qua.quarterName,sub.subName from grade g JOIN student s ON s.stuID = g.stuID JOIN subject sub ON sub.subID = g.subID JOIN quarter qua ON qua.quaID = g.quaID JOIN schoolyear sy ON sy.syID = g.syID where s.IDNO = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        GradesNew grade1 = new GradesNew(rs.getString(1),rs.getString(2),rs.getString(3));
                        result.add(grade1);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
         if(table.equals("subjectManage")) {
            try {
                
                //ps = c.prepareStatement("SELECT DISTINCT s.subname,c.subID FROM subject s JOIN ccs c ON s.subID = c.subID JOIN classlevel cl ON c.classID = cl.classID JOIN curriculum cc ON cc.currID = s.currID WHERE cl.className = '" + gl2 + "' AND cc.currName = '" + gl12 + "'");
                ps = c.prepareStatement("SELECT DISTINCT subject.subName, subject.subID FROM subject JOIN cls ON subject.subID = cls.subID Join classlevel on classlevel.classID = cls.classID  WHERE classlevel.className = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Subject subj = new Subject(rs.getString(1),rs.getString(2));
                        result.add(subj);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
         
          if(table.equals("attendanceManage")) {
            try {
                //ps = c.prepareStatement("SELECT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID JOIN currclsec sa ON sa.secID = sec.secID JOIN sectionadviser saa ON saa.currclsecID = sa.currclsecID JOIN user user ON user.userID = saa.userID WHERE user.userID = '" + a + "' ");
                ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON cl.classID = sec.classID JOIN currclsec sa ON sa.secID = sec.secID JOIN user ON user.userID = sa.userID WHERE user.userID = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Attendance attendance2 = new Attendance(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                        result.add(attendance2);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
         if(table.equals("attendanceManage2")) {
            try {
                ps = c.prepareStatement("SELECT s.idNo, a.month, a.NoOfPre, a.noOfAbs, a. NoOfTardy, a.stuID FROM attendance a JOIN student s ON a.stuID = s.stuID WHERE s.IDNo = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Attendance attendance2 = new Attendance(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                        result.add(attendance2);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }  
          
       if(table.equals("attendanceManage3")) {
            try {
                ps = c.prepareStatement("SELECT s.idNo, s.stuID FROM student s WHERE s.IDNo = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Attendance attendance3 = new Attendance(rs.getString(1), rs.getString(2));
                        result.add(attendance3);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
       
       // APRIL 27
        if (table.equals("getlist4-level-sy")) {
            try {
                //ps = c.prepareStatement("select classlevel.className, curriculum.currName from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID join schoolyear on currsy.syID = schoolyear.syID where currsy.syID = '" + a + "'");
                ps = c.prepareStatement("select distinct classlevel.className, curriculum.currName from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID join schoolyear on currsy.syID = schoolyear.syID where currsy.syID IN (select syID from schoolyear where name = '" + a + "')");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        ClassLevel ws = new ClassLevel(0, rs.getString(1), rs.getString(2));
                        result.add(ws);
                        System.out.println(ps);
                    }
                }
                System.out.println(ps);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist4-level-curr")) {
            try {
                //ps = c.prepareStatement("select schoolyear.name, classlevel.className from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID join schoolyear on currsy.syID = schoolyear.syID where currsy.currID = '" + a + "'");
                ps = c.prepareStatement("select distinct classlevel.className from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID where currsy.currID in (select currID from curriculum where currName = '" + a + "')");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while(rs.next()) {
                        ClassLevel cl = new ClassLevel(0, rs.getString(1));
                        result.add(cl);
                        System.out.println(cl);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        if (table.equals("getlist4-levelsec-sy")) {
            try {
                //ps = c.prepareStatement("select classlevel.className, curriculum.currName from classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID join schoolyear on currsy.syID = schoolyear.syID where currsy.syID = '" + a + "'");
                ps = c.prepareStatement("select classlevel.classNamefrom classlevel join currcl on classlevel.classID = currcl.classID join currsy on currcl.currsyID = currsy.currsyID join curriculum on currsy.currID = curriculum.currID join schoolyear on currsy.syID = schoolyear.syID where currsy.syID IN (select syID from schoolyear where name = '" + a + "')");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        ClassLevel ws = new ClassLevel(0, rs.getString(1));
                        result.add(ws);
                        System.out.println(ps);
                    }
                }
                System.out.println(ps);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
          if(table.equals("gradeManage2")) {
            try {
                ps = c.prepareStatement("SELECT DISTINCT s.IDNo,concat(s.stuLName , ', ' , s.stuGName , ' ' , s.stuMName)Name, cl.className, sec.secName, sy.name, s.oldGenAverage, cur.currName,s.stuID FROM student s JOIN section sec ON s.secID = sec.secID JOIN schoolyear sy ON s.syID = sy.syID JOIN classlevel cl ON s.classID = cl.classID JOIN curriculum cur ON s.currID = cur.currID where cl.className = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                 if (rs != null) {
                    while (rs.next()) {
                        Grades1 grade3 = new Grades1(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getString(7),rs.getString(8));
                        result.add(grade3);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
         if (table.equals("getlist4-elem")) {
              try {
                  ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, className, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID where oldGenAverage > 74 and classlevel.className = '"+a+"' ORDER BY oldGenAverage desc;");
                  rs = ps.executeQuery();
                  result = new ArrayList<Object>();
                  if (rs != null) {
                      while (rs.next()) {
                          Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
                          result.add(s);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
          
          if (table.equals("getlist4-nursery")) {
              try {
                  ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, className, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID where classlevel.className = '"+a+"' ORDER BY schoolyear.name desc;");
                  rs = ps.executeQuery();
                  result = new ArrayList<Object>();
                  if (rs != null) {
                      while (rs.next()) {
                          Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
                          result.add(s);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
          
          if (table.equals("getlist4-highschool")) {
              try {
                  ps = c.prepareStatement("select IDNo, stuLName, stuGName, stuMName, schoolyear.name, className, clevelcat.categoryName, oldGenAverage from student join schoolyear on student.syID = schoolyear.syID join classlevel on student.classID = classlevel.classID join clevelcat on classlevel.clcatID = clevelcat.clcatID where oldGenAverage > 74 and categoryName = 'High School' AND className = '"+a+"' ORDER BY className desc;");
                  rs = ps.executeQuery();
                  result = new ArrayList<Object>();
                  if (rs != null) {
                      while (rs.next()) {
                          Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
                          result.add(s);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
          
          if (table.equals("getlist4-seclvl")) {
              try {
                  ps = c.prepareStatement("select secName, classlevel.className from section join classlevel on section.classID = classlevel.classID where className = '"+a+"'");
                  rs = ps.executeQuery();
                  result = new ArrayList<Object>();
                  if (rs != null) {
                      while (rs.next()) {
                          Sections s = new Sections(rs.getString(1));
                          result.add(s);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
          
           if(table.equals("report1")) {
            try {
                ps = c.prepareStatement("SELECT s.IDno, s.stuLName, s.stuGName, s.stuMName, s.cAddress, s.bDate, s.birthplace, s.gender, s.cAffiliation, s.cNo, s.fName ,s.fOccu,s.fCNo, s.mName, s.mOccu, s.mCNo, c.className FROM student s JOIN classlevel c ON s.classID = c.classID WHERE s.IDNo = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                
                if (rs != null) {
                    while (rs.next()) {
                        Report report1 = new Report(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13), rs.getString(14),rs.getString(15), rs.getString(16),rs.getString(17));
                        result.add(report1);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
          if(table.equals("quarter")) {
            try {
                ps = c.prepareStatement("Select * from quarter where status = '" + a + "'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        Quarter q = new Quarter(rs.getString(1), rs.getString(2) , rs.getString(3));
                        result.add(q);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
          
          if (table.equals("getlist4-secslots")) {
              try {
                  ps = c.prepareStatement("select slots from section where secName = '"+a+"'");
                  rs = ps.executeQuery();
                  result = new ArrayList<Object>();
                  if (rs != null) {
                      while (rs.next()) {
                          Sections s = new Sections(rs.getString(1));
                          result.add(s);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
        // end
        return result;
    }
      
    public Object get(String table, String a, String b) {
        Object result = null;
        if(table.equals("get-user")) {
            try{
                ps = c.prepareStatement("Select userID, userLName, userGName, userMName, userName, password, userStatus, userType From user LEFT JOIN usert ON user.usertID = usert.usertID where userName = '" + b + "'");
                rs = ps.executeQuery();

                if (rs != null) {
                    while(rs.next()) {
                        result = new User(rs.getString(1), rs.getString(2) , rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        result.toString();
                    }
                }
            } catch(Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage());
            }    
        }
        
        if(table.equals("get-endsy")) {
            try {
                ps = c.prepareStatement("SELECT syID FROM schoolyear WHERE endOfClass = '" + a + "'");
                rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        result = rs.getString("name");
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        
        if(table.equals("get-curriculum")) {
            try {
                ps = c.prepareStatement("SELECT * from curriculum where currName = '" + a + "'");
                rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        result = new Curriculum(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        
        if(table.equals("get-sy")) {
            try {
                ps = c.prepareStatement("SELECT * from schoolyear where schoolyear.name = '" + a + "'");
                rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        result = new SchoolYear(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5));
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        
        if(table.equals("get-currsyID")) {
            try {
                ps = c.prepareStatement("SELECT currsyID from currsy where currID = '" + a + "' AND syID = '" + b + "'");
                rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        result = rs.getInt(1) + "";
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        
        if(table.equals("get-currclsecID")) {
            try {
                ps = c.prepareStatement("SELECT currclsecID from currclsec where secID = '" + a + "' AND currclID = '" + b + "'");
                rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        result = rs.getInt(1) + "";
                    }
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        
        if(table.equals("get-secadv")) {
            try {
                ps = c.prepareStatement("select user.userID, user.userLName, user.userGName, user.userMName from user join currclsec on user.userID = currclsec.userID where currclsec.secID = '" + a + "' and currclsec.currclID = '" + b + "'");
                rs = ps.executeQuery();
                

                if (rs != null) {
                    while(rs.next()) {
                        result = new User(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                    }
                } else {
                    result = null;
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }       
        
        if(table.equals("get-currclID")) {
            try {
                ps = c.prepareStatement("select currclID from currcl  where classID = '" + a + "' and currsyID = '" + b + "'");
                rs = ps.executeQuery();
                
                if(rs != null) {
                    while(rs.next()) {
                        result = rs.getString(1);
                    }
                } else {
                    result = null;
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
        if(table.equals("get-currclsecID")) {
            try {
                ps = c.prepareStatement("select currclID from currcl  where classID = '" + a + "' and currsyID = '" + b + "'");
                rs = ps.executeQuery();
                
                if(rs != null) {
                    while(rs.next()) {
                        result = rs.getString(1);
                    }
                } else {
                    result = null;
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
        // APRIL 30
        if(table.equals("get-student")) {
            try {
                ps = c.prepareStatement("SELECT stuID, IDNo, stuLName, stuGName, stuMName, cAddress, bDate, birthPlace, gender, cAffiliation, cNo, fName, fOccu, fCNo, mName, mOccu, mCNo, lSchoolAttended, lSAdd, oldGenAverage, student.secID, section.secName, schoolyear.syID, schoolyear.name, student.classID, className, student.currID, curriculum.currName FROM student LEFT JOIN section ON student.secID = section.secID LEFT JOIN classlevel ON student.classID = classlevel.classID LEFT JOIN schoolyear ON student.syID = schoolyear.syID JOIN curriculum ON student.currID = curriculum.currID WHERE student.stuID = '" + a + "'");
                rs = ps.executeQuery();

                if (rs != null) {
                    while(rs.next()) {
                        result = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13), rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18), rs.getString(19), rs.getString(20), rs.getString(21), rs.getString(22), rs.getString(23), rs.getString(24), rs.getString(25), rs.getString(26), rs.getString(27), rs.getString(28));
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
//        if (table.equals("get-event")) {
//            try {
//                ps = c.prepareStatement("select schoolyear.name, event.eveName, event.startDate, event.endDate, schoolyear.numOfDays from event join schoolyear on event.syID = schoolyear.syID where schoolyear.name = '"+ a +"'");
//                rs = ps.executeQuery();
//                if (rs != null) {
//                    while(rs.next()) {
//                        result = new Event(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
//                    }
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
        // end
        
//        if (table.equals("get-usersearch")) {
//            try {
//                ps = c.prepareStatement("select username, usert.usertype from user join usert on user.usertID = usert.usertID where usertype= '" + a + "'");
//                rs = ps.executeQuery();
//                result = new ArrayList<Object>();
//                
//                if (rs != null) {
//                    while (rs.next()) {
//                        result = new User(rs.getString(1), rs.getString(2));
//                    }
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
        
        //Summer
        if(table.equals("get-summer")) {
            try {
                ps = c.prepareStatement("SELECT sSumID, IDNo, stuLName, stuGName, stuMName, gender, parent, parentcNo, sprogsy.syName FROM studsummer LEFT JOIN sprogsy ON studsummer.spSYID = sprogsy.spSYID WHERE studsummer.IDNo = '" + a + "' AND sprogsy.syName = '" + b + "'");
                rs = ps.executeQuery();

                if (rs != null) {
                    while(rs.next()) {
                        result = new Summer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9));
                        result.toString();
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    
    public Event getEvent(String event) {
        Event ee = null;
        try {
            ps = c.prepareStatement("select schoolyear.name, event.eveName, event.startDate, event.endDate, schoolyear.numOfDays from event join schoolyear on event.syID = schoolyear.syID where schoolyear.name = '"+ event +"'");
            rs = ps.executeQuery();
                if (rs != null) {
                    while(rs.next()) {
                        ee = new Event(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                        ee.toString();
                    }
                }
         } catch (Exception e) {
            e.printStackTrace();
         }
        return ee;
    }
    
    public SchoolYear getDays(String days) {
        SchoolYear sy = null;
        try {
            ps = c.prepareStatement("select name, numOfDays from schoolyear where name = '"+days+"'");
            rs = ps.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    sy = new SchoolYear(rs.getString(1), rs.getInt(2));
                    sy.toString();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }        
        return sy;
    }
    
    public boolean add(String table, Object a, String clvl, User aa) {
        boolean success = false;
        if(table.equals("add-curriculum")) {
            try {
                Curriculum cu = (Curriculum) a;
                Statement st = c.createStatement();
                String newCurr = "INSERT INTO curriculum(currName, dateEncoded, curriculum.desc, dateEffective) VALUES ('" + cu.getCurrName() + "', '" + cu.getCurrDate() + "', '" + cu.getCurrDesc() + "', '" + cu.getCurrEffective() + "')";
                st.executeUpdate(newCurr);
                success = true;
                sf.appendTextArea(aa.getUsername() + " : Added a new Curriculum - " + cu.getCurrName());
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }   
        }
        
        if(table.equals("add-sy")) {
            try {
                SchoolYear sy = (SchoolYear) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO schoolyear (name, startOfClass, endOfClass, numOfDays) VALUES ('" + sy.getSyName() + "', '" + sy.getSyStart() + "', '" + sy.getSyEnd() + "', '" + sy.getNumOfDays() + "')";
                s.executeUpdate(query);
                success = true;
                sf.appendTextArea(aa.getUsername() + " : Added a new School Year - " + sy.getSyName());
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        
        if(table.equals("add-currsy")) {
            try {
                SchoolYear sy = (SchoolYear) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO currsy (currID, syID) VALUES ('" + sy.getCurrID() + "', '" + sy.getSyID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        
        if(table.equals("add-currcl")) {
            try {
                ClassLevel cc = (ClassLevel) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO currcl (classID, currsyID) VALUES ('" + cc.getClassID() + "', '" + cc.getCurrSYID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        
        if(table.equals("add-currcls")) {
            try {
                Subject cc = (Subject) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO currcls (currclID, subID) VALUES ('" + cc.getCurrclID() + "', '" + cc.getsubID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        
        if(table.equals("add-currclsec")) {
            try {
                Sections cc = (Sections) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO currclsec (secID, currclID, userID) VALUES ('" + cc.getSecID() + "', '" + cc.getCurrclID() + "', '" + cc.getAdv().getID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }        
        
        if(table.equals("add-classlevel")) {
            try {
                ClassLevel cc = (ClassLevel) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO classlevel (className, clcatID) VALUES ('" + cc.getClassName() + "', '" + cc.getCatID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }    
        
        if(table.equals("add-subject")) {
            try {
                Subject ss = (Subject) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO subject (subName, subject.desc, unit, classID) VALUES ('" + ss.getSubjName() + "', '" + ss.getDesc() + "', '" + ss.getUnits() + "', '" + ss.getClassID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }        
        
        if(table.equals("add-section")) {
            try {
                Sections ss = (Sections) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO section (secName, classID) VALUES ('" + ss.getSect() + "', '" + ss.getClassID() + "')";
                s.executeUpdate(query);
                success = true;
            }catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        // APRIL 28
        if(table.equals("add-student")) {
            try {
                Student stu = (Student) a; 
                
                ps = c.prepareStatement("Select classID From classlevel Where classlevel.className = '" + stu.getClassLevel() + "'");
                rs = ps.executeQuery();
                int cl = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        cl = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("Select currID From curriculum Where curriculum.currName = '" + stu.getCurrID() + "'");
                rs = ps.executeQuery();
                int cu = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        cu = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("Select syID From schoolyear Where schoolyear.name = '" + stu.getSchoolYear() + "'");
                rs = ps.executeQuery();
                int sr = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        sr = rs.getInt(1);
                    }
                }
                
                Statement s = c.createStatement();
                String query = "INSERT INTO student(IDNo, stuLName, stuGName, stuMName, cAddress, bDate, birthPlace, gender, cAffiliation, cNo, fName, fOccu, fCNo, mName, mOccu, mCNo, lSchoolAttended, lSAdd, oldGenAverage, classID, currID, syID) VALUES ('" + stu.getSID() + "','" + stu.getLName() + "','" + stu.getGName() + "','" + stu.getMNane() + "','" + stu.getCAddress() + "','" + stu.getBirthday() + "','" + stu.getBirthPlace() + "','" + stu.getGender() + "','" + stu.getChurchAffiliate() + "','" + stu.getContact() + "','" + stu.getFatherName() + "','" + stu.getFatherOccupation() + "','" + stu.getFatherContact() + "','" + stu.getMotherName() + "','" + stu.getMotherOccupation() + "','" + stu.getMotherContact() + "','" + stu.getLastSchoolAttended() + "','" + stu.getLastSchoolAddres() + "','" + stu.getGeneralAverage() + "','" + cl + "', '" + cu + "', '" + sr + "')";
                s.executeUpdate(query);
                success = true;
            } catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        // end
        
        // APRIL 30
        if (table.equals("add-classadviser")) {
            try {
                Adviser ad = (Adviser) a;
                
                ps = c.prepareStatement("select caddID from classadviser where classadviser.caddID = '"+ad.getAdviserID()+"'");
                rs = ps.executeQuery();
                int ca = 0;
                if (rs != null) {
                    while(rs.next()) {
                        ca = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("select userID from user where user.userID = '"+ad.getUserID()+"'");
                rs = ps.executeQuery();
                int u = 0;
                if (rs != null) {
                    while (rs.next()) {
                        u = rs.getInt(1);
                    }
                }
                
                Statement s = c.createStatement();
                String query = "INSERT INTO advisercat (caddID, userID) VALUES ('"+ca+"', '"+u+"')";
                success = true;
            } catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("add-user")) {
            String  stat = "Out";        
            try{
                User u = (User) a;
                ps = c.prepareStatement("Select usertID From usert Where userType = '" + u.getType() + "'");
                rs = ps.executeQuery();
                int ut = 0;

                if(rs != null) {
                    while(rs.next()) {
                        ut = rs.getInt(1);
                    }
                }

                Statement s = c.createStatement();
                String query = "INSERT INTO user(userLName, userGName, userMName, userName, password, userStatus, usertID) VALUES ('" + u.getLName() + "','" + u.getGName() + "','" + u.getIMName() + "','" + u.getUsername() + "','" + u.getPassword() + "','" +  stat + "','" + ut + "')";
                s.executeUpdate(query);            
               // sf.appendTextArea(a.getUsername() + " : Added a new User - " + u.getUsername());
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        // end
        
        // APRIL 27
        if (table.equals("add-summersy")) {
            try {
                SummerSy ssy = (SummerSy) a;
                Statement s = c.createStatement();
                String query = "INSERT INTO sprogsy(syName, startDate, endDaye, numOfDays) VALUES ('" + ssy.getSyName() + "', '" + ssy.getSyStart() + "', '" + ssy.getSyEnd() + "', '" + ssy.getNumOfDays() + "')";
                s.executeUpdate(query);
                success = true;
            } catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        // end
        
        if (table.equals("add-promote")) {
            try {
                Student stu = (Student) a; 
                
                ps = c.prepareStatement("select secID from section where section.secName = '"+stu.getSection()+"'");
                rs = ps.executeQuery();
                int se = 0;
                if (rs != null) {
                    while(rs.next()) {
                        se = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("Select classID From classlevel Where classlevel.className = '" + stu.getClassLevel() + "'");
                rs = ps.executeQuery();
                int cl = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        cl = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("Select currID From curriculum Where curriculum.currName = '" + stu.getCurrID() + "'");
                rs = ps.executeQuery();
                int cu = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        cu = rs.getInt(1);
                    }
                }
                
                ps = c.prepareStatement("Select syID From schoolyear Where schoolyear.name = '" + stu.getSchoolYear() + "'");
                rs = ps.executeQuery();
                int sr = 0;
                
                if(rs != null) {
                    while(rs.next()) {
                        sr = rs.getInt(1);
                    }
                }
                
                Statement s = c.createStatement();
                String query = "INSERT INTO student(IDNo, stuLName, stuGName, stuMName, cAddress, bDate, birthPlace, gender, cAffiliation, cNo, fName, fOccu, fCNo, mName, mOccu, mCNo, lSchoolAttended, lSAdd, oldGenAverage, secID, classID, currID, syID) VALUES ('" + stu.getSID() + "','" + stu.getLName() + "','" + stu.getGName() + "','" + stu.getMNane() + "','" + stu.getCAddress() + "','" + stu.getBirthday() + "','" + stu.getBirthPlace() + "','" + stu.getGender() + "','" + stu.getChurchAffiliate() + "','" + stu.getContact() + "','" + stu.getFatherName() + "','" + stu.getFatherOccupation() + "','" + stu.getFatherContact() + "','" + stu.getMotherName() + "','" + stu.getMotherOccupation() + "','" + stu.getMotherContact() + "','" + stu.getLastSchoolAttended() + "','" + stu.getLastSchoolAddres() + "','" + stu.getGeneralAverage() + "', '"+se+"'," + cl + "', '" + cu + "', '" + sr + "')";
                s.executeUpdate(query);
                success = true;
            } catch (Exception e) {
                success = false;
                e.printStackTrace();
            }
        }
        return success;
    }
    
    public String addEvent(Event ee, User a) {
        try {
            ps = c.prepareStatement("SELECT syID FROM schoolyear WHERE name = '" + ee.getSyID() + "'");
            rs = ps.executeQuery();
            int sy = 0;
            if (rs != null) {
                while (rs.next()) {
                    sy = rs.getInt(1);
                }
            }
            
            Statement st = c.createStatement();
            String ac = "INSERT INTO event(eveName, startDate, endDate, remarks, syID) VALUES ('" + ee.getEventName() + "', '" + ee.getStartDate() + "', '" + ee.getEndDate() + "', '"+ee.getRemarks()+"', '" + sy + "')";
            st.executeUpdate(ac);
            sf.appendTextArea(a.getUsername() + " : Added an event.");
        }catch (Exception e) {
            e.printStackTrace();
           // JOptionPane.showMessageDialog(null, e.getMessage());
        }
        return "Event Successfully Added";    
    }
    // end
    
    // APRIL 26
    public String getEndSy(String end) {
        try {
            ps = c.prepareStatement("SELECT name FROM schoolyear WHERE endOfClass = '" + end + "'");
            rs = ps.executeQuery();
            int en = 0;
            if (rs != null) {
                while (rs.next()) {
                    en = rs.getInt(1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }    
        return "end";     
    }
        
    public String getStartSy(String start) {
        try {
            ps = c.prepareStatement("SELECT name FROM schoolyear WHERE startOfClass = '" + start + "'");
            rs = ps.executeQuery();
            int st = 0;
            if (rs != null) {
                while (rs.next()) {
                    st = rs.getInt(1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "start";
    }
    // end
    
    
     public String addGrade(double a1, int a2, int a3, String a4, String a5) {
        try {
            ps = c.prepareStatement("SELECT subID FROM subject where subName ='" + a4 + "'");
            rs = ps.executeQuery();
            int aG= 0;
            if(rs != null){
                while(rs.next()){
                       aG = rs.getInt(1); 
                }
            }
            
             ps = c.prepareStatement("SELECT sy.syID FROM schoolyear sy where sy.name ='" + a5 + "'");
            rs = ps.executeQuery();
            int aQ= 0;
            if(rs != null){
                while(rs.next()){
                       aQ = rs.getInt(1); 
                }
            }
            
            Statement st = c.createStatement();
            String ac = "INSERT INTO grade(grade, stuID, subID, quaID, syID) VALUES ('" + a1 + "', '" + a2 + "', '" + aG + "', '" + a3 + "', '" + aQ + "')";
            //String ac = "INSERT INTO grade(firstG, secondG, thirdG, fourthG, average, graStatus, stuID, subID) VALUES ('" + ab + "', '" + ab1 + "', '" + ab2 + "', '" + ab3 + "', '" + a4 + "', '" + a5 + "', '" + a6 + "', '" + aG + "')";
            // String ac = "UPDATE grade SET firstG = '" + a + "', secondG = '" + a1 + "', thirdG = '" + a2 + "', fourthG =  '" + a3 + "', average = '" + a4 + "'";
            st.executeUpdate(ac);
         // sf.appendTextArea(a.getUsername() + " : Added a new Student Grade.");
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            e.printStackTrace();
        }
        return "Grade Successfully Added";    
    }
     
      public String editGrade(double a1, int a2, int a3, String a4, String a5) {
        try {
            ps = c.prepareStatement("SELECT subID FROM subject where subName ='" + a4 + "'");
            rs = ps.executeQuery();
            int aG= 0;
            if(rs != null){
                while(rs.next()){
                       aG = rs.getInt(1); 
                }
            }
            
             ps = c.prepareStatement("SELECT sy.syID FROM schoolyear sy where sy.name ='" + a5 + "'");
            rs = ps.executeQuery();
            int aQ= 0;
            if(rs != null){
                while(rs.next()){
                       aQ = rs.getInt(1); 
                }
            }
            
            ps = c.prepareStatement("SELECT stuID from student where IDNO = '" + a2 + "'");
            rs = ps.executeQuery();
            int aS= 0;
            if(rs != null){
                while(rs.next()){
                       aS = rs.getInt(1); 
                }
            }
            
            Statement st = c.createStatement();
            String ac = "UPDATE grade SET grade = '" + a1 + "', stuID =  '" + aS + "', subID = '" + aG + "', quaID = '" + a3 + "', syID = '" + aQ + "' WHERE quaID = '" + a3 + "'";
            st.executeUpdate(ac);
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            e.printStackTrace();
        }
        return "Grade Successfully Added";    
    }
    
     
      public String addAttendance(String a, int a1, int a2, int a3, int a4, String a5) {
        try {
//            ps = c.prepareStatement("SELECT eveID FROM event WHERE name = '" + ee.getEvID() + "'");
//            rs = ps.executeQuery();
//            int sy = 0;
//            if (rs != null) {
//                while (rs.next()) {
//                    sy = rs.getInt(1);
//                }
//            }
            
            Statement st = c.createStatement();
            String ac = "INSERT INTO attendance(month, noOfPre, noOfAbs, noOfTardy, noOfSchoolDays, stuID) VALUES ('" + a + "', '" + a1 + "', '" + a2 + "', '" + a3 + "', '" + a4 + "', '" + a5 + "')";
            st.executeUpdate(ac);
           // sf.appendTextArea(aa.getUsername() + " : Added a new Attendance.");
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
       // sf.appendTextArea(aa.getUsername() + " : Added an Attendance.");
        return "Attendance Successfully Added";    
    }
     
      
       public String editAttendance(String a, int a1, int a2, int a3, int a4, String a5) {
        try {
            Statement st = c.createStatement();
            String ac = "UPDATE attendance SET noOfPre = '" + a1 + "', noOfAbs = '" + a2 + "',noOfTardy = '" + a3 + "', NoOfSchoolDays = '" + a4 + "'  WHERE stuID = '" + a5 + "' AND month = '" + a + "'";
            st.executeUpdate(ac);
           // sf.appendTextArea(aa.getUsername() + " : Updated an Attendance.");
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
       // sf.appendTextArea(aa.getUsername() + " : Updated an Attendance.");
        return "Attendance Successfully Edited";    
    }
    
       // Summer Student and Summer Subject    
    public ArrayList<Object> SSList() {
        ArrayList<Object> result = null;
        try {
            ps = c.prepareStatement("SELECT summerprogram.progID, summerprogram.name FROM summerprogram");
            rs = ps.executeQuery();
            result = new ArrayList<Object>();
            
            if(rs != null) {
                while(rs.next()) {
                    SumSubject ss = new SumSubject(rs.getString(1), rs.getString(2));
                    result.add(ss);
                }   
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public ArrayList<Object> SumList(String sy) {
        ArrayList<Object> result = null;
        try {
            ps = c.prepareStatement("SELECT summerprogram.progID, summerprogram.name FROM summerprogram LEFT JOIN sssy ON summerprogram.progID = sssy.progID LEFT JOIN sprogsy ON sssy.spSYID = sprogsy.spSYID WHERE sprogsy.syName = '" + sy + "'");
            rs = ps.executeQuery();
            result = new ArrayList<Object>();
            
            if(rs != null) {
                while(rs.next()) {
                    SumSubject ss = new SumSubject(rs.getString(1), rs.getString(2));
                    result.add(ss);
                }   
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public ArrayList<Object> SSSList(String a) {
        ArrayList<Object> result = null;
        try {
            ps = c.prepareStatement("SELECT summerprogram.progID, summerprogram.name FROM summerprogram LEFT JOIN ssprog USING(progID) LEFT JOIN studsummer USING(sSumID) WHERE studsummer.IDNo = '" + a + "'");
            rs = ps.executeQuery();
            result = new ArrayList<Object>();
            
            if(rs != null) {
                while(rs.next()) {
                    SumSubject ss = new SumSubject(rs.getString(1), rs.getString(2));
                    result.add(ss);
                }   
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public Object SumSub(String a, String b) {
        Object result = null;
        try {
            ps = c.prepareStatement("SELECT summerprogram.progID, summerprogram.name, sprogSY.syName FROM summerprogram LEFT JOIN ssSY USING(progID) LEFT JOIN sprogSY USING(spSYID) WHERE summerprogram.name = '" + a + "' AND sprogSY.syName = '" + b + "'");
            rs = ps.executeQuery();
            
            if(rs != null) {
                while(rs.next()) {
                    result = new SumSubject(rs.getString(1), rs.getString(2), rs.getString(3));
                    result.toString();
                }   
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public String getSummerSave(Summer sum, String sy, ArrayList<Object> subList, User a) {
        String m = null;
        try {
            ps = c.prepareStatement("SELECT spSYID FROM sprogsy WHERE sprogsy.syName = '" + sy + "'");
            rs = ps.executeQuery();
            int syi = 0;
            
            if(rs != null) {
                while(rs.next()) {
                    syi = rs.getInt(1);
                }
            }            
            
            Statement st = c.createStatement();            
            String newPro = "INSERT INTO studsummer(IDNo, stuLName, stuGName, stuMName, gender, parent, parentcNo, spSYID) VALUES ('" + sum.getSSID() + "','" + sum.getSSLName() + "','" + sum.getSSGName() + "','" + sum.getSSMName() + "','" + sum.getSGen() + "','" + sum.getSParent() + "','" + sum.getSParentCno() + "','" + syi + "')";
            st.executeUpdate(newPro);
            
            ps = c.prepareStatement("SELECT sSumID FROM studsummer WHERE studsummer.IDNo = '" + sum.getSSID() + "'");
            rs = ps.executeQuery();
            int ssi = 0;
            
                if(rs != null) {
                    while(rs.next()) {
                        ssi = rs.getInt(1);
                    }
                }
                        
            int p = 0;
            while(p < subList.size()) {
                String ss = (String) subList.get(p); 
                
                ps = c.prepareStatement("SELECT progID FROM summerprogram WHERE summerprogram.name = '" + ss + "'");
                rs = ps.executeQuery();
                int sli = 0;
            
                    if(rs != null) {
                        while(rs.next()) {
                            sli = rs.getInt(1);
                        }
                    }    
            
                Statement st1 = c.createStatement();
                String proSub = "INSERT INTO ssprog(sSumID, progID) VALUES ('" + ssi + "','" + sli + "')";
                st1.executeUpdate(proSub);
                p++;
            }           
            
            sf.appendTextArea(a.getUsername() + " : Added a new Summer Student - " + sum.getSSLName() + ", " + sum.getSSGName() + " " + sum.getSSMName());
            m = "Student Added Successfully";
        }catch (Exception e) {
            e.printStackTrace();
            m = "Failed in Adding Student";
        }
        return m;
    }
    
    public String editManage(String table, String Orig, Object o, User a) {
        String m = null;
        
        if(table.equals("sumstud")) {
            try {
                Summer cl = (Summer) o;
                
                Statement st = c.createStatement();
                String update = "UPDATE studsummer SET stuLName = '" + cl.getSSLName() + "', stuGName = '" + cl.getSSGName() + "', stuMName = '" + cl.getSSMName() + "', parent = '" + cl.getSParent() + "', parentcNo = '" + cl.getSParentCno() + "'  WHERE sSumID = '" + Orig + "'";
                st.executeUpdate(update);
                sf.appendTextArea(a.getUsername() + " : Updated a Summer Student - " + cl.getSSLName() + ", " + cl.getSSGName() + " " + cl.getSSMName());
                m = "Update  Successful.";
            }catch (Exception e) {
                e.printStackTrace();
                m = "Update Failed.";
            }   
        }                
        return m;
    } 
    
    public String SumSubAdd(String nSub) {
        String m = null;
        try {
            Statement st = c.createStatement();
            String update = "INSERT INTO summerprogram (name) VALUES ('" + nSub + "')";
            st.executeUpdate(update);
            
            m = "Summer Subject Successfully Added";
        }catch (Exception e) {
            e.printStackTrace();
            m = "Failed in Adding Subject";
        }
        return m;
    }
    
    public String SumSubjectAdd(ArrayList<Object> nSub, String sSY) {
        String m = null;
        try {
            ps = c.prepareStatement("SELECT sprogsy.spSYID FROM sprogsy WHERE sprogsy.syName = '" + sSY + "'");
            rs = ps.executeQuery();
            int spy = 0;
            
                if(rs != null) {
                    while(rs.next()) {
                        spy = rs.getInt(1);
                    }
                }
            int p = 0;
            while(p < nSub.size()) {    
                String ns = (String) nSub.get(p);
                ps = c.prepareStatement("SELECT summerprogram.progID FROM summerprogram WHERE summerprogram.name = '" + ns + "'");
                rs = ps.executeQuery();
                int sp = 0;
            
                    if(rs != null) {
                        while(rs.next()) {
                            sp = rs.getInt(1);
                        }
                    }
                               
                Statement st1 = c.createStatement();
                String update1 = "INSERT INTO ssSY (progID, spSYID) VALUES ('" + sp + "', '" + spy + "')";
                st1.executeUpdate(update1);
                p++;
            }    
        }catch (Exception e) {
            e.printStackTrace();
        }
        return m;
    } 
    
    public ArrayList<Object> cbList(String t) {
        ArrayList<Object> cbr = null;
        if(t.equals("cb-summer")) {
            try {
                ps = c.prepareCall("SELECT sprogsy.syName FROM sprogsy");
                rs = ps.executeQuery();
                cbr = new ArrayList<Object>();
                String res = null;
                if(rs!=null) {
                    while(rs.next()) {
                        res = rs.getString(1);
                        cbr.add(res);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }            
        }
        
        if(t.equals("classcat")) {
            try {
                ps = c.prepareCall("SELECT categoryName FROM clevelcat");
                rs = ps.executeQuery();
                cbr = new ArrayList<Object>();
                String res = null;
                if(rs!=null) {
                    while(rs.next()) {
                        res = rs.getString(1);
                        cbr.add(res);
                    }
                }
            }catch (Exception e) {
                e.printStackTrace();
            }            
        }
        return cbr;
    }
    
    // APRIL 30
    public boolean edit(String table, Object a, User aa) {
        boolean yehey = false;
        if (table.equals("edit-student")) {
            try { 
                Student stu = (Student) a;
                Statement s = c.createStatement();
                String query = "UPDATE student SET stuLName = '" +stu.getLName()+"', stuGName = '"+stu.getGName()+"', stuMName = '"+stu.getMNane()+"', cAddress = '"+stu.getCAddress()+"', bDate = '"+stu.getBirthday()+"', birthPlace = '"+stu.getGender()+"', cAffiliation = '"+stu.getChurchAffiliate()+"', cNo = '"+stu.getContact()+"', fName = '"+stu.getFatherName()+"', fOccu='"+stu.getFatherOccupation()+"', fCNo='"+stu.getFatherContact()+"', mName='"+stu.getMotherName()+"', mOccu='"+stu.getMotherOccupation()+"', mCNo='"+stu.getMotherContact()+"', lSchoolAttended='"+stu.getLastSchoolAttended()+"', lSAdd='"+stu.getLastSchoolAddres()+"', oldGenAverage='"+stu.getGeneralAverage()+"' Where stuID = '" + stu.getStuID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-classlevel")) {
            try { 
                ClassLevel clevel = (ClassLevel) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE classlevel SET className = '"+clevel.getClassName()+"', clcatID = '" +clevel.getCatID()+"' where classID = '" + clevel.getClassID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-section")) {
            try { 
                Sections ss = (Sections) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE section SET secName = '"+ss.getSect()+"', classID = '" +ss.getClassID()+"' where secID = '" + ss.getSecID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-subject")) {
            try { 
                Subject ss = (Subject) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE subject SET subName = '"+ss.getSubjName()+"', subject.desc = '" +ss.getDesc()+"', unit = '" + ss.getUnits() + "', classID = '" + ss.getClassID() + "' where subID = '" + ss.getsubID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-sy")) {
            try { 
                SchoolYear ss = (SchoolYear) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE schoolyear SET endOfClass = '"+ss.getSyEnd()+"', numOfDays = '" +ss.getNumOfDays() + "' where name = '" + ss.getSyName() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-section")) {
            try { 
                Sections ss = (Sections) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE section SET secName = '"+ss.getSect()+"', classID = '" +ss.getClassID()+"' where secID = '" + ss.getSecID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-subject")) {
            try { 
                Subject ss = (Subject) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE subject SET subName = '"+ss.getSubjName()+"', subject.desc = '" +ss.getDesc()+"', unit = '" + ss.getUnits() + "', classID = '" + ss.getClassID() + "' where subID = '" + ss.getsubID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        if (table.equals("edit-clevelsec")) {
            try { 
                String ss = a.toString();
                Statement s = c.createStatement();
                String query = "Delete from currclsec where currclID = '" + ss + "'";
                s.executeUpdate(query);
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        
        if (table.equals("edit-studsec")) {
            try { 
                Student ss = (Student) a;
                
                Statement s = c.createStatement();
                String query = "UPDATE student SET secID = '"+ss.getSecID()+"' where stuID = '" + ss.getStuID() + "'";
                s.executeUpdate(query);                                 
                yehey = true;
            } catch (Exception e) {
                yehey = false;
                e.printStackTrace();
            }
        }
        return yehey;
    }

    // end
    
  
    
}
